package com.elitel.frame.main.dao.ext;

import java.util.List;
import java.util.Map;

import com.elitel.frame.main.entity.CfgTable;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author guoyanfei
 * @Description 扩展数据库表mapper
 */
public interface ExtCfgTableMapper {

    /**
     * @param dbKey
     * @return List<CfgTable>
     * @Description 通过数据源标识查询表信息
     * @author guoyanfei
     */
    List<CfgTable> getListBydbkey(String dbKey);

    /**
     * @param dbKey
     * @return List<CfgTable>
     * @Description 通过数据源标识查询所有表信息(不包括视图)
     * @author guoyanfei
     */
    List<CfgTable> getTableListBydbkey(String dbKey);

    /**
     * @param dbkey(数据源标识),tableType(表类型)
     * @param tableType
     * @return CfgTable集合
     * @Description 通过数据源标识和表类型查询表信息
     * @author guoyanfei
     */
    List<CfgTable> getListBydbkeyAndType(@Param("dbkey") String dbkey, @Param("tableType") Integer tableType);

    /**
     * @return 数量
     * @Description 查询CfgTable中是否存在dtconfigId
     * @author gyf
     */
    String getdtConfigIdNum(String dtconfigId);

    /**
     * @return CfgTable实体
     * @Description 通过表标识查询CfgTable信息
     * @author gyf
     */
    CfgTable getInfoByDtconfigId(String dtConfigId);

    /**
     * @param dtConfigId(表标识)
     * @param tableType(表类型)
     * @return CfgTable实体
     * @Description 通过表标识和表类型查询表信息
     * @author guoyanfei
     */
    CfgTable getTableBydtConfigIdAndtableType(@Param("dtConfigId") String dtConfigId, @Param("tableType") Integer tableType);

    /**
     * @return CfgTable
     * @throws
     * @Title: queryCfgTable
     * @Description: TODO(单表服务查询表名及数据源连接信息)
     * @author Fanwenquan
     */
    CfgTable queryCfgTable(String dtConfigId);

    /**
     * @return 返回删除结果int类型
     * @throws
     * @Description: 通过表标识(dtConfigId)删除表信息
     * @author gyf
     */
    int deleteTableBydtConfigId(String dtConfigId);

    /**
     * @param dbkey(数据库主键),tbname(表名称)
     * @return 返回删除结果int类型
     * @throws
     * @Description: 通过表名称和dbkey查询表信息
     * @author gyf
     */
    CfgTable selectTableBynameanddbkey(@Param("dbkey") String dbkey, @Param("tbname") String tbname);

    String getBiaoName(String biao);

    /**
     * @param dtConfigId(视图dtConfigId)
     * @return CfgTable实体
     * @Descirption 通过视图dtConfigId查询视图主表信息
     * @author guoyanfei
     */
    CfgTable getDataViewBydtconfigId(String dtConfigId);

    List<CfgTable> selectAllShortTable(Map<String, Object> map);

    /**
     * show 分页查询mysql数据库中配置表信息
     * create by guoyanfei on 2018/12/25
     *
     * @param beginRow  开始条数
     * @param endRow    结束条数
     * @param dbkey     数据库标识
     * @param paramtxt  参数条件
     * @param tabletype 表类型(1数据表,2数据视图)
     * @return CfgTable实体list集合
     */
    List<CfgTable> getListbyPageinmysql(@Param("beginRow") Integer beginRow,
                                        @Param("endRow") Integer endRow,
                                        @Param("dbkey") String dbkey,
                                        @Param("paramtxt") String paramtxt,
                                        @Param("tabletype") Integer tabletype);

    List<CfgTable> getListbyPageinPG(@Param("beginRow") Integer beginRow,
                                     @Param("endRow") Integer endRow,
                                     @Param("dbkey") String dbkey,
                                     @Param("paramtxt") String paramtxt,
                                     @Param("tabletype") Integer tabletype);

    /**
     * 分页查询sqlite数据库中配置表信息
     *
     * @param beginRow
     * @param endRow
     * @param dbkey
     * @param paramtxt
     * @param tabletype
     * @return
     */
    List<CfgTable> getListbyPageinSqlite(@Param("beginRow") Integer beginRow,
                                         @Param("endRow") Integer endRow,
                                         @Param("dbkey") String dbkey,
                                         @Param("paramtxt") String paramtxt,
                                         @Param("tabletype") Integer tabletype);

    /***
     * show 分页查询oracle数据库中配置表信息
     * create by guoyanfei on 2018/12/25
     * @param beginRow 开始条数
     * @param endRow 结束条数
     * @param dbkey 数据库标识
     * @param paramtxt 参数条件
     * @param tabletype 表类型(1数据表,2数据视图)
     * @return CfgTable实体list集合
     */
    List<CfgTable> getListbyPageinoracle(@Param("beginRow") Integer beginRow,
                                         @Param("endRow") Integer endRow,
                                         @Param("dbkey") String dbkey,
                                         @Param("paramtxt") String paramtxt,
                                         @Param("tabletype") Integer tabletype);

    /**
     * show 查询Oracle数据库总条数
     * create by guoyanfei on 2018/12/25
     *
     * @param dbkey     数据库标识
     * @param paramtxt  参数条件
     * @param tabletype 表类型(1数据表,2数据视图)
     * @return 结果总条数
     */
    Integer getCountBydbkey(@Param("dbkey") String dbkey,
                            @Param("paramtxt") String paramtxt,
                            @Param("tabletype") Integer tabletype);


    Integer getCountBydbkeySqlite(@Param("dbkey") String dbkey,
                                  @Param("paramtxt") String paramtxt,
                                  @Param("tabletype") Integer tabletype);


    List<CfgTable> getListbyPageTreeinmysql(@Param("beginRow") Integer beginRow,
                                            @Param("endRow") Integer endRow,
                                            @Param("id") String id,
                                            @Param("paramtxt") String paramtxt,
                                            @Param("contentType") Integer contentType);

    List<CfgTable> getListbyPageTreeinPG(@Param("beginRow") Integer beginRow,
                                         @Param("endRow") Integer endRow,
                                         @Param("id") String id,
                                         @Param("paramtxt") String paramtxt,
                                         @Param("contentType") Integer contentType);

    List<CfgTable> getListByPageTreeOthers(@Param("beginRow") Integer beginRow,
                                           @Param("endRow") Integer endRow,
                                           @Param("dt_conn") String dt_conn,
                                           @Param("paramtxt") String paramtxt);

    List<CfgTable> getListByPageTreeOthersPG(@Param("beginRow") Integer beginRow,
                                             @Param("endRow") Integer endRow,
                                             @Param("dt_conn") String dt_conn,
                                             @Param("paramtxt") String paramtxt);

    Integer getCountBydbkeyTree(@Param("id") String id,
                                @Param("paramtxt") String paramtxt,
                                @Param("contentType") Integer contentType);

    Integer getCountBydbkeyTreeOthers(@Param("dt_conn") String dt_conn,
                                      @Param("paramtxt") String paramtxt);

    @Select("select a.typeid,a.dt_cnname from cfg_tabletype a join cfg_catalogue b on a.TYPEID=b.id and b.TYPE=#{module} and lower(b.DBNAME)=lower(#{dbName}) and lower(a.DT_NAME)=lower(#{dtName}) group by a.typeid,a.dt_cnname")
    Map<String, String> getInfoByModuleDtName(@Param("module") Integer module, @Param("dbName") String dbName, @Param("dtName") String dtName);

    @Select("SELECT\n" +
            "\tb.DBNAME AS dbname,\n" +
            "\tA.DT_NAME AS dt_name,\n" +
            "\tMAX ( A.typeid ) AS typeid,\n" +
            "\tMAX ( A.dt_cnname ) AS dt_cnname \n" +
            "FROM\n" +
            "\tcfg_tabletype\n" +
            "\tA JOIN cfg_catalogue b ON A.TYPEID = b.ID \n" +
            "WHERE\n" +
            "\tb.TYPE = #{module} \n" +
            "GROUP BY\n" +
            "\tb.DBNAME,\n" +
            "\tA.DT_NAME")
    List<Map<String, String>> getInfoMapListByModule(@Param("module") Integer module);

    @Select("select hdfsdb,hdfstable from cfg_storage where dtconfig_id=#{dtconfig_id}")
    Map<String, String> getStorageInfo(@Param("dtconfig_id") String dtconfig_id);

    @Select("select id as typeid,NM as typename from cfg_catalogue")
    List<Map<String, String>> getTableTypeMap();

    Integer getRecourdTotalBydbkey(@Param("id") String id,
                                   @Param("paramtxt") String paramtxt,
                                   @Param("contentType") Integer contentType);

    Integer getRecourdTotalBydbkeyOthers(@Param("dt_conn") String dt_conn,
                                         @Param("paramtxt") String paramtxt);

    @Select(
            "select GUID as id,typeid as parentid,concat(ifNUll(DT_NAME_CN,''),'(',dt_name,')') as dtname,DT_CONN as dbname from cfg_table\n" +
                    " union\n" +
                    "select id as id,parent_id as parentid,nm as dtname,DBNAME as dbname from cfg_catalogue where type=#{mark} order by id"
    )
    List<Map<String, String>> getJStreeParams(@Param("mark") Integer mark);

    @Select(
            "select GUID as id,typeid as parentid,concat(COALESCE(DT_NAME_CN,''),'(',dt_name,')') as dtname,DT_CONN as dbname from cfg_table\n" +
                    " union\n" +
                    "select id as id,parent_id as parentid,nm as dtname,DBNAME as dbname from cfg_catalogue where type=#{mark} order by id "
    )
    List<Map<String, String>> getJStreeParamsPG(@Param("mark") Integer mark);

    @Update("update cfg_table set DATAQUANTITY=${dataquantity} where DT_NAME=#{dt_name} and DT_CONN=#{dbnm}")
    Integer updateCfgtableCount(@Param("dataquantity") Integer dataquantity,
                                @Param("dt_name") String dt_name,
                                @Param("dbnm") String dbnm);

    @Update("UPDATE cfg_table a inner join (\n" +
            "select dbname,tabname,rowcount from data_tablecount) \n" +
            "b on lower(a.DT_CONN)=b.dbname and lower(a.DT_NAME)=b.tabname SET a.DATAQUANTITY=b.rowcount")
    Integer refreshCfgtableCount();

    @Update("UPDATE data_centerinfo set dc_capacity=${dataquantity} where dc_id=#{id}")
    Integer refreshCenterinfo(@Param("id") Integer id, @Param("dataquantity") Double dataquantity);

    CfgTable getInfoByGuid(String guid);

}
